package com.cloudweb.oa.controller;

import cn.js.fan.util.ParamUtil;
import cn.js.fan.util.StrUtil;
import cn.js.fan.web.DBInfo;
import cn.js.fan.web.Global;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cloudweb.oa.base.IConfigUtil;
import com.cloudweb.oa.utils.ProxoolUtil;
import com.cloudweb.oa.vo.Result;
import com.cloudwebsoft.framework.util.LogUtil;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;
import org.logicalcobwebs.proxool.ProxoolException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.*;
import org.xml.sax.InputSource;

import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.io.StringReader;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

@RestController
@RequestMapping("/admin")
public class DataSourceController {

    @Autowired
    IConfigUtil configUtil;

    @Autowired
    ProxoolUtil proxoolUtil;

    @Autowired
    HttpServletRequest request;

    @ApiOperation(value = "删除数据源", notes = "删除数据源", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "name", value = "数据源名称", required = true, dataType = "String"),
    })
    @PreAuthorize("hasAnyAuthority('admin')")
    @ResponseBody
    @RequestMapping(value = "/dataSource/delete", method = RequestMethod.POST, produces = {"text/html;charset=UTF-8;", "application/json;"})
    public Result<Object> delete(@RequestParam(value = "name") String name) {
        if ("oa".equals(name)) {
            return new Result<>(false, "不能删除oa");
        }

        try {
            String xml = configUtil.getXml("config_sys");
            SAXBuilder sb = new SAXBuilder();
            Document doc = sb.build(new InputSource(new StringReader(xml)));

            Element root = doc.getRootElement();
            Element which = root.getChild("DataBase");
            // 先删后加
            List list = which.getChildren("db");
            for (Object o : list) {
                Element e = (Element) o;
                if (e.getChildText("name").equals(name)) {
                    which.removeContent(e);
                    break;
                }
            }

            configUtil.putXml("config_sys", doc);
        } catch (JDOMException | IOException e) {
            LogUtil.getLog(getClass()).error(e);
        }

        Document doc = proxoolUtil.getDoc();
        Element root = doc.getRootElement();

        // 先删后加
        List list = root.getChildren();
        for (Object o : list) {
            Element e = (Element) o;
            if (e.getChildText("alias").equals(name)) {
                root.removeContent(e);
                break;
            }
        }

        proxoolUtil.write();

        Global.getInstance().init();

        org.logicalcobwebs.proxool.ProxoolFacade.removeAllConnectionPools(5000); //
        try {
            org.logicalcobwebs.proxool.configuration.JAXPConfigurator.configure(proxoolUtil.getCfgPath(), false);
        } catch (ProxoolException e) {
            LogUtil.getLog(getClass()).error(e);
        }

        return new Result<>();
    }

    @ApiOperation(value = "创建数据源", notes = "创建数据源", httpMethod = "POST")
    @PreAuthorize("hasAnyAuthority('admin')")
    @ResponseBody
    @RequestMapping(value = "/dataSource/create", method = RequestMethod.POST, produces = {"text/html;charset=UTF-8;", "application/json;"})
    public Result<Object> create() {
        String name = ParamUtil.get(request, "name");
        if (!StrUtil.isSimpleCode(name)) {
            return new Result<>(false, "名称只能为字母、数字及符号：-_");
        }

        // 判断是否已存在同名的数据源
        Document docChk = proxoolUtil.getDoc();
        Element rootChk = docChk.getRootElement();
        Element elChk;
        List list = rootChk.getChildren("proxool");
        for (Object value : list) {
            Element e = (Element) value;
            elChk = e.getChild("alias");
            if (elChk.getText().equals(name)) {
                return new Result<>(false,"已存在同名数据源");
            }
        }

        String user = ParamUtil.get(request, "user");
        String pwd = ParamUtil.get(request, "pwd");
        String ip = ParamUtil.get(request, "ip");
        String port = ParamUtil.get(request, "port");
        String database = ParamUtil.get(request, "database");
        //url = "jdbc:mysql://" + ip + ":" + port + "/" + database + "?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull";
        String oadb = ParamUtil.get(request, "oadb");
        String odbcName = ParamUtil.get(request, "odbcName");
        String path = ParamUtil.get(request, "path");

        int max_conn = ParamUtil.getInt(request, "max_conn", 50);
        String className = "", url = "", msg = "";
        boolean isValid = true;

        if ("access".equalsIgnoreCase(oadb)) {
            className = "sun.jdbc.odbc.JdbcOdbcDriver";
            url = "jdbc:odbc:driver={" + odbcName + "};DBQ=" + path;
        } else if (oadb.equalsIgnoreCase(Global.DB_ORACLE)) {
            className = "oracle.jdbc.driver.OracleDriver";
            url = "jdbc:oracle:thin:@//" + ip + ":" + port + "/" + database;
        } else if ("mssql".equalsIgnoreCase(oadb)) {
            className = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
            url = "jdbc:microsoft:sqlserver://" + ip + ":" + port + ";DatabaseName=" + database;
        } else if ("mssql_n".equalsIgnoreCase(oadb)) {
            className = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
            url = "jdbc:sqlserver://" + ip + ":" + port + ";DatabaseName=" + database;
        } else if (oadb.equalsIgnoreCase(Global.DB_MYSQL)) {
            className = "com.mysql.cj.jdbc.Driver";
            url = "jdbc:mysql://" + ip + ":" + port + "/" + database + "?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=Asia/Shanghai";
        } else if (oadb.equalsIgnoreCase(Global.DB_POSTGRESQL)) {
            className = "org.postgresql.Driver";
            url = "jdbc:postgresql://" + ip + ":" + port + "/" + database;
        }

        try {
            Class.forName(className);
        } catch (java.lang.ClassNotFoundException e) {
            isValid = false;
            msg = e.getMessage();
        }
        try {
            java.sql.Connection con = DriverManager.getConnection(url, user, pwd);
            con.close();
        } catch (SQLException e) {
            isValid = false;
            msg = e.getMessage();
        }

        if (isValid) {
            try {
                String xml = configUtil.getXml("config_sys");
                SAXBuilder sb = new SAXBuilder();
                Document doc = sb.build(new InputSource(new StringReader(xml)));

                Element root = doc.getRootElement();
                Element which = root.getChild("DataBase");
                Element edb = new Element("db");

                Element e = new Element("name");
                e.addContent(name);
                edb.addContent(e);

                e = new Element("Default");
                e.addContent("false");
                edb.addContent(e);

                e = new Element("UsePool");
                e.addContent("false");
                edb.addContent(e);

                e = new Element("PoolName");
                e.addContent("");
                edb.addContent(e);

                e = new Element("DBDriver");
                e.addContent("org.logicalcobwebs.proxool.ProxoolDriver");
                edb.addContent(e);

                e = new Element("ConnStr");
                e.addContent("proxool." + name);
                edb.addContent(e);

                which.addContent(edb);

                configUtil.putXml("config_sys", doc);
            } catch (JDOMException | IOException e) {
                LogUtil.getLog(getClass()).error(e);
            }

            Document doc = proxoolUtil.getDoc();

            Element root = doc.getRootElement();
            Element edb = new Element("proxool");

            Element e = new Element("alias");
            e.addContent(name);
            edb.addContent(e);

            e = new Element("driver-url");
            e.addContent(url);
            edb.addContent(e);

            e = new Element("driver-class");
            e.addContent(className);
            edb.addContent(e);

            e = new Element("driver-properties");
            Element el = new Element("property");
            el.setAttribute("name", "user");
            el.setAttribute("value", user);
            e.addContent(el);
            el = new Element("property");
            el.setAttribute("name", "password");
            el.setAttribute("value", pwd);
            e.addContent(el);
            edb.addContent(e);

            e = new Element("maximum-connection-count");
            e.addContent(String.valueOf(max_conn));
            edb.addContent(e);

            e = new Element("house-keeping-test-sql");
            e.addContent("select 1");
            edb.addContent(e);

            root.addContent(edb);

            proxoolUtil.write();

            Global.getInstance().init();

            org.logicalcobwebs.proxool.ProxoolFacade.removeAllConnectionPools(5000); //
            try {
                org.logicalcobwebs.proxool.configuration.JAXPConfigurator.configure(proxoolUtil.getCfgPath(), false);
            } catch (ProxoolException ex) {
                LogUtil.getLog(getClass()).error(ex);
            }
        }

        if (!isValid) {
            return new Result<>(false, msg);
        } else {
            return new Result<>();
        }
    }

    @ApiOperation(value = "编辑数据源页面", notes = "编辑数据源页面", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "name", value = "数据源名称", required = true, dataType = "String"),
    })
    @PreAuthorize("hasAnyAuthority('admin')")
    @ResponseBody
    @RequestMapping(value = "/dataSource/editPage", method = RequestMethod.POST, produces = {"text/html;charset=UTF-8;", "application/json;"})
    public Result<Object> editPage(@RequestParam(value = "name") String name) {
        return new Result<>(getDataSource(name));
    }

    public JSONObject getDataSource(String name) {
        String oadb = "", user = "", pwd = "", ip = "", port = "", database = "", url = "", maximum_connection_count = "", odbcName = "", path = "";

        String className = "";
        String msg = "";
        Document doc = proxoolUtil.getDoc();

        Element root = doc.getRootElement();
        Element el = null;
        List list = root.getChildren("proxool");
        for (Object value : list) {
            Element e = (Element) value;
            el = e.getChild("alias");
            if (el.getText().equals(name)) {
                el = e.getChild("driver-url");
                url = el.getText();
                el = e.getChild("driver-class");
                className = el.getText();
                el = e.getChild("maximum-connection-count");
                maximum_connection_count = el.getText();

                el = e.getChild("driver-properties");
                for (Object o : el.getChildren("property")) {
                    Element ep = (Element) o;
                    String attrName = ep.getAttributeValue("name");
                    String attrVal = ep.getAttributeValue("value");
                    if ("user".equals(attrName)) {
                        user = attrVal;
                    } else {
                        pwd = attrVal;
                    }
                }

                if (url.startsWith("jdbc:odbc:driver")) {
                    oadb = "access";
                    int p = url.indexOf("{");
                    int q = url.indexOf("}");
                    odbcName = url.substring(p, q + 1).trim();
                    p = url.lastIndexOf("=");
                    path = url.substring(p).trim();
                } else if (url.startsWith("jdbc:oracle:thin")) {
                    oadb = "Oracle";
                    int p = url.indexOf("@//") + 3;
                    int q = url.indexOf(":", p);
                    ip = url.substring(p, q);
                    p = url.indexOf("/", q);
                    port = url.substring(q + 1, p);
                    database = url.substring(p + 1);
                } else if (url.startsWith("jdbc:microsoft:sqlserver")) {
                    oadb = "mssql";
                    int p = url.indexOf("//") + 2;
                    int q = url.indexOf(":", p);
                    ip = url.substring(p, q);
                    p = url.indexOf(";", q);
                    port = url.substring(q + 1, p);
                    p = url.lastIndexOf("=");
                    database = url.substring(p + 1);
                } else if (url.startsWith("jdbc:sqlserver")) {
                    oadb = "mssql_n";
                    int p = url.indexOf("//") + 2;
                    int q = url.indexOf(":", p);
                    ip = url.substring(p, q);
                    p = url.indexOf(";", q);
                    port = url.substring(q + 1, p);
                    p = url.lastIndexOf("=");
                    database = url.substring(p + 1);
                } else if (url.startsWith("jdbc:mysql")) {
                    oadb = "mysql";
                    int p = url.indexOf("//") + 2;
                    int q = url.indexOf(":", p);
                    ip = url.substring(p, q);
                    p = url.indexOf("/", q);
                    port = url.substring(q + 1, p);
                    q = url.indexOf("?");
                    database = url.substring(p + 1, q);
                } else if (url.startsWith("jdbc:postgresql")) {
                    oadb = "postgresql";
                    int p = url.indexOf("//") + 2;
                    int q = url.indexOf(":", p);
                    ip = url.substring(p, q);
                    p = url.indexOf("/", q);
                    port = url.substring(q + 1, p);
                    database = url.substring(p + 1);
                }
                break;
            }
        }

        JSONObject json = new JSONObject();
        json.put("name", name);
        json.put("database", database);
        json.put("user", user);
        json.put("pwd", pwd);
        json.put("ip", ip);
        json.put("port", port);
        json.put("type", oadb);
        json.put("odbcName", odbcName);
        json.put("maxConn", maximum_connection_count);
        json.put("oadb", oadb);
        return json;
    }

    @ApiOperation(value = "保存数据源", notes = "保存数据源", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "name", value = "数据源名称", required = true, dataType = "String"),
    })
    @PreAuthorize("hasAnyAuthority('admin')")
    @ResponseBody
    @RequestMapping(value = "/dataSource/update", method = RequestMethod.POST, produces = {"text/html;charset=UTF-8;", "application/json;"})
    public Result<Object> update(@RequestParam(value = "name") String name) {
        String oadb="", user="", pwd="", ip="", port="", database="", url="", odbcName = "", path="";
        String className = "";
        user = ParamUtil.get(request, "user");
        pwd = ParamUtil.get(request, "pwd");
        ip = ParamUtil.get(request, "ip");
        port = ParamUtil.get(request, "port");
        database = ParamUtil.get(request, "database");
        oadb = ParamUtil.get(request, "oadb");
        odbcName = ParamUtil.get(request, "odbcName");
        path = ParamUtil.get(request, "path");

        int maxConn = ParamUtil.getInt(request, "maxConn", 200);

        if ("access".equalsIgnoreCase(oadb)) {
            className = "sun.jdbc.odbc.JdbcOdbcDriver";
            url = "jdbc:odbc:driver={" + odbcName + "};DBQ=" + path;
        } else if (oadb.equalsIgnoreCase(Global.DB_ORACLE)) {
            className = "oracle.jdbc.driver.OracleDriver";
            url = "jdbc:oracle:thin:@//" + ip + ":" + port + "/" + database;
        } else if ("mssql".equalsIgnoreCase(oadb)) {
            className = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
            url = "jdbc:microsoft:sqlserver://" + ip + ":" + port + ";DatabaseName=" + database;
        } else if ("mssql_n".equalsIgnoreCase(oadb)) {
            className = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
            url = "jdbc:sqlserver://" + ip + ":" + port + ";DatabaseName=" + database;
        } else if (oadb.equalsIgnoreCase(Global.DB_MYSQL)) {
            className = "com.mysql.cj.jdbc.Driver";
            url = "jdbc:mysql://" + ip + ":" + port + "/" + database + "?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=Asia/Shanghai";
        } else if (oadb.equalsIgnoreCase(Global.DB_POSTGRESQL)) {
            className = "org.postgresql.Driver";
            url = "jdbc:postgresql://" + ip + ":" + port + "/" + database;
        }

        boolean isValid = true;
        String msg = "";
        try {
            Class.forName(className);
        }
        catch (java.lang.ClassNotFoundException e) {
            isValid = false;
            msg = e.getMessage();
        }
        try {
            java.sql.Connection con = DriverManager.getConnection(url, user, pwd);
            con.close();
        }
        catch (SQLException e) {
            isValid = false;
            msg = e.getMessage();
        }

        if (isValid) {
            try {
                String xml = configUtil.getXml("config_sys");
                SAXBuilder sb = new SAXBuilder();
                Document doc = sb.build(new InputSource(new StringReader(xml)));

                Element root = doc.getRootElement();
                Element which = root.getChild("DataBase");
                // 先删后加
                List list = which.getChildren("db");
                for (Object o : list) {
                    Element e = (Element) o;
                    if (e.getChildText("name").equals(name)) {
                        which.removeContent(e);
                        break;
                    }
                }

                Element edb = new Element("db");

                Element e = new Element("name");
                e.addContent(name);
                edb.addContent(e);

                e = new Element("Default");
                e.addContent("false");
                edb.addContent(e);

                e = new Element("UsePool");
                e.addContent("false");
                edb.addContent(e);

                e = new Element("PoolName");
                e.addContent("");
                edb.addContent(e);

                e = new Element("DBDriver");
                e.addContent("org.logicalcobwebs.proxool.ProxoolDriver");
                edb.addContent(e);

                e = new Element("ConnStr");
                e.addContent("proxool." + name);
                edb.addContent(e);

                which.addContent(edb);

                configUtil.putXml("config_sys", doc);
            } catch (JDOMException | IOException e) {
                LogUtil.getLog(getClass()).error(e);
            }

            try {
                Document doc = proxoolUtil.getDoc();

                Element root = doc.getRootElement();

                // 先删后加
                List list = root.getChildren();
                for (Object o : list) {
                    Element e = (Element) o;
                    if (e.getChildText("alias").equals(name)) {
                        root.removeContent(e);
                        break;
                    }
                }

                Element edb = new Element("proxool");

                Element e = new Element("alias");
                e.addContent(name);
                edb.addContent(e);

                e = new Element("driver-url");
                e.addContent(url);
                edb.addContent(e);

                e = new Element("driver-class");
                e.addContent(className);
                edb.addContent(e);

                e = new Element("driver-properties");
                Element el = new Element("property");
                el.setAttribute("name", "user");
                el.setAttribute("value", user);
                e.addContent(el);
                el = new Element("property");
                el.setAttribute("name", "password");
                el.setAttribute("value", pwd);
                e.addContent(el);
                edb.addContent(e);

                e = new Element("maximum-connection-count");
                e.addContent(String.valueOf(maxConn));
                edb.addContent(e);

                e = new Element("house-keeping-test-sql");
                e.addContent("select 1");
                edb.addContent(e);

                root.addContent(edb);

                proxoolUtil.write();

                Global.getInstance().init();

                org.logicalcobwebs.proxool.ProxoolFacade.removeAllConnectionPools(5000);
                org.logicalcobwebs.proxool.configuration.JAXPConfigurator.configure(proxoolUtil.getCfgPath(), false);
            } catch (ProxoolException e) {
                LogUtil.getLog(getClass()).error(e);
            }
        }

        if (!isValid) {
            return new Result<>(false, msg);
        } else {
            return new Result<>();
        }
    }

    @ApiOperation(value = "数据源列表", notes = "数据源列表", httpMethod = "POST")
    @PreAuthorize("hasAnyAuthority('admin')")
    @ResponseBody
    @RequestMapping(value = "/dataSource/list", method = RequestMethod.POST, produces = {"text/html;charset=UTF-8;", "application/json;"})
    public Result<Object> list() {
        JSONArray ary = new JSONArray();
        cn.js.fan.web.Config cfg = new cn.js.fan.web.Config();
        for (Object o : cfg.getDBInfos()) {
            DBInfo di = (DBInfo) o;
            if (di.isDefault) {
                continue;
            }
            JSONObject json = new JSONObject();
            json.put("name", di.name);
            json.put("isDefault", di.isDefault);
            json.put("value", di.name);

            json.putAll(getDataSource(di.name));

            ary.add(json);
        }
        return new Result<>(ary);
    }
}
